""""
Case Type   : D库常用语法兼容-常用函数兼容
Case Name   : 查看sys.database视图返回值为0测试
Create At   : 2025-10-10
Owner       : chen-czywj
Description :
    1.使用兼容D库创建数据库
    2.创建数据库执行测试
    3.查看视图返回值正确无误
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.查看视图返回值正确无误
    4.清理环境
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class Function_Commonviews_Case0020(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.dbname = 'test_views_001'
        self.db1 = 'db1_commonviews_020'
        self.db2 = 'db2_commonviews_020'

    def test_Function_Commonviews(self):
        text = '-------------step1: 使用兼容D库创建数据库; expect: 成功-------------'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_db = f'''drop database if exists {self.dbname};
            create database if not exists {self.dbname} dbcompatibility = 'D';'''
        res = primary_sh.execut_db_sql(create_db, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res)
        load_extension = primary_sh.execut_db_sql('create extension shark;',
                                     dbname=self.dbname)
        log.info(load_extension)
        self.assertIn(self.constant.create_extension_success, load_extension)

        text = '-------------step2: 创建数据库执行测试; expect: 成功-------------'
        log.info(text)
        create_db = f'''create database {self.db1};
            create database {self.db2};'''
        res = primary_sh.execut_db_sql(create_db, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res)

        text = '-------------step3: 查看视图返回值正确无误; expect: 返回值正确-------------'
        log.info(text)
        select_sql = f'''select * from sys.databases where database_id = 1;'''
        res = primary_sh.execut_db_sql(select_sql, dbname=self.dbname)
        log.info(res)
        self.assertIn(r"template1 |           1 |                    |        10 |      " \
            r"       |                     | SQL_ASCII      | 0           | MULTI_USER  " \
            r"     | 0             | 0                | 0                 | 0     | ONLINE" \
            r"     | 0             | 0                   | 0                            " \
            r"   | 1                        | ON                            | 1         " \
            r"                    | 1              | FULL                | 0               " \
            r"   |                         | 1                       | 0                " \
            r"                   | 0                       | 0                          " \
            r"   | 1                       | 1                | 0                  | 0" \
            r"                   | 1                | 1                             | 0 " \
            r"                       | 1                       | 0                      " \
            r"  | 0                            | 0                       | 0          " \
            r"         | 0                 | 0                 | 0                      " \
            r"    | 0                                 | 0                 | 0           " \
            r" | 0             | 0                  | 0              | 0              " \
            r"     |                     | 0                 | 0              | NOTHING " \
            r"            | 0                      | 0              | 0            | 0  " \
            r"                         |            |                   |             " \
            r"     |                       |                       |                    " \
            r"            |                                |                       |    " \
            r"                         |                       | 0           | NONE   " \
            r"          |                               0 |                  0 |        " \
            r"                 | 0                                          | 0         " \
            r"           | 0                              | 0                         " \
            r"  | 0                                     | 0                      | " \
            r"Not Applicable              |                        | 0                   " \
            r"     | 0                                   | 0                              " \
            r" | 0                          | 0                           | 0            " \
            r"| 0                      | 0", res)

    def tearDown(self):
        text = '------------------step4: 清理环境; expect: 成功------------------'
        log.info(text)
        drop_db = f'''drop database if exists {self.dbname};
            drop database if exists {self.db1};
            drop database if exists {self.db2};'''
        res = primary_sh.execut_db_sql(drop_db, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res)
